import datetime
import hashlib
import logging
import os.path
from urllib.parse import urlparse

import psycopg2
import requests
import urllib3
from apify_client import ApifyClient
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

# 禁用不安全请求的警告
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)
# 配置日志
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

IMAGE_DIR = '/data/wwwroot/tictok/upload/img'
VIDEO_DIR = '/data/wwwroot/tictok/upload/video'

PROXIES = {
'http': 'http://127.0.0.1:8890',
}

oldest_post_date = (datetime.date.today() - datetime.timedelta(days=1)).isoformat()
# Initialize the ApifyClient with your API token
client = ApifyClient("apify_api_8SXVpzZ7JOykQzag6jON0dipnNcWNG3Gfcal")

# Prepare the Actor input
run_input = {
    "hashtags": [
        "rtppt",
        "china",
        "chinese",
        "cnnportugal"
    ],
    "profiles": [
        "RTP",
        "CNN Portugal"
    ],
    "searchQueries": [""],
    "proxyCountryCode": "None",
    "oldestPostDate": oldest_post_date,
    "resultsPerPage": 10,
    "scrapeLastNDays": 1,
    "excludePinnedPosts": False,
    "shouldDownloadCovers": True,
    "shouldDownloadSlideshowImages": False,
    "shouldDownloadSubtitles": False,
    "shouldDownloadVideos": True
}

# 数据库连接配置
DB_CONFIG = {
    'dbname': 'tictok_media',
    'user': 'postgres',
    'password': 'Tech@2024',
    'host': 'localhost',
    'port': '5432'
}
def connect_to_db():
    logging.info("连接数据库")
    return psycopg2.connect(**DB_CONFIG)
def setup_database(cursor):
    logging.info("初始化数据库")
    # 创建表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tictok_posts (
        id TEXT PRIMARY KEY,
        text TEXT,
        create_time INT,
        create_time_iso TIMESTAMP,
        is_ad BOOLEAN,
        is_muted BOOLEAN,
        author_id TEXT,
        web_video_url TEXT,
        digg_count INT,
        share_count INT,
        play_count INT,
        collect_count INT,
        comment_count INT,
        is_slideshow BOOLEAN,
        is_pinned BOOLEAN,
        input TEXT
    );
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tictok_author_meta (
        id TEXT PRIMARY KEY,
        profile_url TEXT,
        name TEXT,
        nick_name TEXT,
        verified BOOLEAN,
        signature TEXT,
        bio_link TEXT,
        avatar TEXT,
        private_account BOOLEAN,
        tt_seller BOOLEAN,
        following INT,
        fans INT,
        heart INT,
        video INT,
        digg INT
    );
    """)

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tictok_music_meta (
        music_id TEXT PRIMARY KEY,
        post_id TEXT,
        music_name TEXT,
        music_author TEXT,
        music_original BOOLEAN,
        music_album TEXT,
        play_url TEXT,
        cover_medium_url TEXT
    );
    """)
    # 创建视频元数据表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS tictok_video_meta (
        post_id TEXT,
        height INT,
        width INT,
        duration INT,
        cover_url TEXT,
        original_cover_url TEXT,
        definition TEXT,
        format TEXT,
        original_download_addr TEXT,
        download_addr TEXT,
        PRIMARY KEY (post_id)
    );
    """)
    logging.info("Database setup completed.")

def create_retry_session(retries = 3, backoff_factor = 0.3, status_forcelist=(500.502,504)):
    session = requests.Session()
    retry = Retry(
        total=retries,
        read=retries,
        connect=retries,
        backoff_factor=backoff_factor,
        status_forcelist=status_forcelist,
    )
    adapter = HTTPAdapter(max_retries=retry)
    session.mount('http://', adapter)
    session.mount('https://', adapter)
    return session
def download_image(url):
    if not url:
        logging.warning(f"No URL provided for image download")
        return None
    if not os.path.exists(IMAGE_DIR):
        os.makedirs(IMAGE_DIR)
        logging.info(f"Created directory: {IMAGE_DIR}")

    # 使用URL的MD5哈希作为文件名，以确保唯一性
    url_hash = hashlib.md5(url.encode()).hexdigest()
    file_extension = os.path.splitext(urlparse(url).path)[1] or '.jpg'
    filename = f"{url_hash}{file_extension}"

    local_path = os.path.join(IMAGE_DIR, filename)

    # 检查文件是否已存在
    if os.path.exists(local_path):
        logging.info(f"Image already exists locally: {local_path}")
        return local_path

    session = create_retry_session()

    try:
        logging.info(f"Downloading image: {url}")
        response = session.get(url, stream=True, proxies=PROXIES, verify=False, timeout=30)
        response.raise_for_status()
        with open(local_path, 'wb') as file:
            for chunk in response.iter_content(chunk_size=8192):
                file.write(chunk)
        logging.info(f"Image downloaded successfully: {local_path}")
        return local_path
    except requests.RequestException as e:
        logging.error(f"Error downloading image from {url}: {e}")
        return None

def download_video(url, filename):
    # 检查文件是否已存在
    if os.path.exists(filename):
        logging.info(f"Video already exists locally: {filename}")
        return filename

    # 检查文件是否已存在
    try:
        # 向 URL 发送 GET 请求
        response = requests.get(url, stream=True, proxies=PROXIES, verify=False, timeout=180)
        if response.status_code == 200:
            # 打开一个本地文件用于写入
            with open(filename, 'wb') as file:
                for chunk in response.iter_content(chunk_size=8192):
                    # 写入视频数据到本地文件
                    file.write(chunk)
            logging.info(f"视频已下载并保存为 {filename}")
            return filename
    except requests.RequestException as e:
        logging.error(f"Error downloading image from {url}: {e}")
        return None


def upsert_posts(cursor, data):
    post_id = data.get('id')
    author_id = data['authorMeta']['id']
    logging.info(f"Upserting posts: {post_id} - {author_id}")

    # Check if the post already exists
    cursor.execute("SELECT COUNT(*) FROM tictok_posts WHERE id = %s", (post_id,))
    if cursor.fetchone()[0] == 0:
        # Insert the data
        cursor.execute("""
           INSERT INTO tictok_posts (id, text, create_time, create_time_iso, is_ad, is_muted, author_id, web_video_url, digg_count, share_count, play_count, collect_count, comment_count, is_slideshow, is_pinned, input)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
           """, (
            data['id'],
            data['text'],
            data['createTime'],
            data['createTimeISO'],
            data['isAd'],
            data['isMuted'],
            data['authorMeta']['id'],
            data['webVideoUrl'],
            data['diggCount'],
            data['shareCount'],
            data['playCount'],
            data['collectCount'],
            data['commentCount'],
            data['isSlideshow'],
            data['isPinned'],
            data['input']
        ))

def upsert_author(cursor, author):
    author_id = author['id']

    logging.info(f"Upserting author: {author_id}")

    # Check if the author already exists
    cursor.execute("SELECT COUNT(*) FROM tictok_author_meta WHERE id = %s", (author_id,))
    if cursor.fetchone()[0] == 0:
        # Insert the author data
        cursor.execute("""
        INSERT INTO tictok_author_meta (id, profile_url, name, nick_name, verified, signature, bio_link, avatar, private_account, tt_seller, following, fans, heart, video, digg)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            author['id'],
            author['profileUrl'],
            author['name'],
            author['nickName'],
            author['verified'],
            author['signature'],
            author['bioLink'],
            author['avatar'],
            author['privateAccount'],
            author['ttSeller'],
            author['following'],
            author['fans'],
            author['heart'],
            author['video'],
            author['digg']
        ))
        # 下载封面图片并替换本地图片路径
        avatar_url = author['avatar']
        if avatar_url:
            local_avatar_path = download_image(avatar_url)
            if local_avatar_path:
                cursor.execute("UPDATE tictok_author_meta SET avatar = %s WHERE id = %s",
                               (local_avatar_path, author['id']))


def upsert_music(cursor, data):
    music_id = data['musicMeta']['musicId']

    logging.info(f"Upserting music: {music_id}")

    # Check if the music already exists
    cursor.execute("SELECT COUNT(*) FROM tictok_music_meta WHERE music_id = %s", (music_id,))
    if cursor.fetchone()[0] == 0:
        # Insert the music data
        cursor.execute("""
        INSERT INTO tictok_music_meta (music_id, post_id, music_name, music_author, music_original, music_album, play_url, cover_medium_url)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """, (
            data['musicMeta']['musicId'],
            data['id'],
            data['musicMeta']['musicName'],
            data['musicMeta']['musicAuthor'],
            data['musicMeta']['musicOriginal'],
            data['musicMeta']['musicAlbum'],
            data['musicMeta']['playUrl'],
            data['musicMeta']['coverMediumUrl']
        ))

def upsert_video(cursor, data):
    cursor.execute("SELECT COUNT(*) FROM tictok_video_meta WHERE post_id = %s", (data['id'],))
    if cursor.fetchone()[0] == 0:
        # 插入视频元数据
        cursor.execute("""
            INSERT INTO tictok_video_meta (post_id, height, width, duration, cover_url, original_cover_url, definition, format, original_download_addr, download_addr)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
            data['id'],  # 确保这里与 posts 表的 id 对应，用作外键或关联键
            data['videoMeta']['height'],
            data['videoMeta']['width'],
            data['videoMeta']['duration'],
            data['videoMeta']['coverUrl'],
            data['videoMeta']['originalCoverUrl'],
            data['videoMeta']['definition'],
            data['videoMeta']['format'],
            data['videoMeta']['originalDownloadAddr'],
            data['videoMeta']['downloadAddr']
        ))

        # 下载视频并替换本地视频路径和图片
        video_url = data['videoMeta']['downloadAddr']
        if video_url:
            video_format = '.'+data['videoMeta']['format'] or '.mp4'
            video_name = os.path.join(VIDEO_DIR,  data['id']+video_format)
            local_video_path = download_video(video_url,video_name)
            if local_video_path:
                cursor.execute("UPDATE tictok_video_meta SET download_addr = %s WHERE post_id = %s",
                               (local_video_path, data['id']))

        # 下载封面图片并替换本地图片路径
        cover_url = data['videoMeta']['coverUrl']
        if cover_url:
            local_cover_path = download_image(cover_url)
            if local_cover_path:
                cursor.execute("UPDATE tictok_video_meta SET cover_url = %s WHERE post_id = %s",
                               (local_cover_path, data['id']))

def get_all_data():
    # Run the Actor and wait for it to finish
    run = client.actor("GdWCkxBtKWOsKjdch").call(run_input=run_input)
    return client.dataset(run["defaultDatasetId"])


def insert_tictok():
    conn = None
    cursor = None
    try:
        conn = connect_to_db()
        cursor = conn.cursor()
        setup_database(cursor)

        # 获取并处理数据集中的每个项目
        for item in get_all_data().iterate_items():
            post_id = item.get('id')
            if not post_id:
                logging.error(f"No post ID provided for upserting posts")
                logging.error(item)
                continue

            try:
                # 执行所有的 INSERT 语句
                upsert_posts(cursor, item)
                upsert_author(cursor, item['authorMeta'])
                upsert_music(cursor, item)
                upsert_video(cursor, item)

                # 每处理完一个项目就提交一次事务
                conn.commit()
                logging.info(f"成功处理并提交项目: {item['id']}")
            except Exception as item_error:
                # 如果处理单个项目时出错，回滚该项目的更改并继续处理下一个
                conn.rollback()
                logging.error(f"处理项目 {item['id']} 时出错: {item_error}")
    except Exception as e:
        logging.error(f"整体处理过程中出错: {e}")
    finally:
        # 确保资源被正确关闭
        if cursor:
            cursor.close()
        if conn:
            conn.close()
        logging.info("数据库连接已关闭")



if __name__ == "__main__":
    logging.info(f"开始导入TicTok数据")
    insert_tictok()
    logging.info(f"导入TicTok数据结束")


